﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using DBA.Data;
using DBA.Data.ADO;
using DBA.Bll;
using DBA.Common;


namespace DBA.Web.Analysis
{
    public partial class EmployeeDetailAnalysis : DBA.Web.UI.AdminPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack) 
            {
                hidEmpoyeeID.Value = Request.Params["EmployeeID"];
                hidYear.Value = Request.Params["Year"];
                BindProject();
                BindPaper();
                BindBook();
                BindTeaching();
                BindInterView();
                BindTeacherStudent();
                BindTeaherPaper();
                BindListenCourse();
                BindOther();
                BindService();
            }
        }
        /// <summary>
        /// 科研项目
        /// </summary>
        public void BindProject() 
        {
            string sql = "select a.*,b.name from SinProject a inner join EmployeeInfo b on a.sp_person=b.employeeid  where (a.sp_Person=@sp_Person and DATEPART(year,sp_EndTime)>=@year) or exists(select 1 from YearMoney b where a.sp_ID=b.sp_ID and SUBSTRING(ym_year,0,CHARINDEX('-',ym_year)) =@year) ";

            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("sp_Person", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repProject.DataSource = dt;
            repProject.DataBind();

        }
        /// <summary>
        /// 科研项目年度经费汇总
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string getYearMoney(string id)
        {
            string rtn = string.Empty;
            string sql = " select SUBSTRING(ym_year,1,4) as year,SUM(ym_Money) as money from YearMoney where sp_id=@sp_id group by SUBSTRING(ym_year,1,4) order by SUBSTRING(ym_year,1,4) desc";

            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("sp_id", id);

            DataTable dt = dal.ExecuteFillDataTable(sql);
            foreach (DataRow dr in dt.Rows) 
            {
                rtn += dr["year"].ToString() + "年度到款:" + dr["money"].ToString()+"万元";
                rtn += "<br/>";
            }

            rtn = rtn == "" ? "无" : rtn;
            return rtn;
        }
        /// <summary>
        /// 科研论文
        /// </summary>
        public void BindPaper()
        {
            string sql = "select a.* from PaperSum a ";
            sql += " left join EmployeeInfo b on a.ps_Person1=b.Name and Person1Is=1 ";
            sql += " left join EmployeeInfo c on a.ps_Person2=c.Name and Person2Is=1 ";
            sql += " left join EmployeeInfo d on a.ps_Person3=d.Name and Person3Is=1 ";
            sql += " left join EmployeeInfo e on a.ps_Person4=e.Name and Person4Is=1 ";

            sql += " where (b.EmployeeID=@id or c.EmployeeID=@id or d.EmployeeID=@id or e.EmployeeID=@id) ";
            sql += " SUBSTRING(ps_date,0,CHARINDEX('-',ps_date))==@year";
            sql += " order by a.ps_date desc";
            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("id", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repPaper.DataSource = dt;
            repPaper.DataBind();
        }
        /// <summary>
        /// 著作
        /// </summary>
        public void BindBook() 
        {
            string sql = "select a.*  from  PaperSum3 a ";

            sql += " left join EmployeeInfo b on a.ps_Person1=b.Name and Person1Is=1 ";
            sql += " left join EmployeeInfo c on a.ps_Person2=c.Name and Person2Is=1 ";
            sql += " left join EmployeeInfo d on a.ps_Person3=d.Name and Person3Is=1 ";
            sql += " left join EmployeeInfo e on a.ps_Person4=e.Name and Person4Is=1 ";
            sql += " left join EmployeeInfo f on a.ps_Person5=f.Name and Person5Is=1 ";

            sql += " where (b.EmployeeID=@id or c.EmployeeID=@id or d.EmployeeID=@id or e.EmployeeID=@id or f.EmployeeID=@id) ";
            sql += " and SUBSTRING(ps_date,0,CHARINDEX('-',ps_date))=>=@year";
            sql += " order by a.ps_date desc";

            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("id", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repBook.DataSource = dt;
            repBook.DataBind();
        }
        /// <summary>
        /// 教学
        /// </summary>
        public void BindTeaching() 
        {
            string sql = "select a.* from TeacherCourse a where a.EmployeeID=@id and Year=@year order by Grade ";


            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("id", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repTeaching.DataSource = dt;
            repTeaching.DataBind();

        }
        /// <summary>
        /// 入学面试
        /// </summary>
        public void BindInterView() 
        {
            string sql = "select a.* from TeacherReview a where a.EmployeeID=@id and Year=@year order by Grade ";


            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("id", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repInterview.DataSource = dt;
            repInterview.DataBind();
        }


        /// <summary>
        /// 入学面试
        /// </summary>
        public void BindTeacherStudent()
        {
            string sql = "select a.* from TeacherStudent a where a.EmployeeID=@id and Year=@year order by Grade ";


            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("id", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repTeacherStudent.DataSource = dt;
            repTeacherStudent.DataBind();
        }


        public void BindListenCourse()
        {
            string sql = "select a.* from TeacherListenCourse a where a.EmployeeID=@id and Year=@year order by Grade ";


            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("id", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repListenCourse.DataSource = dt;
            repListenCourse.DataBind();
        }

        public void BindOther()
        {
            string sql = "select a.* from OtherWork a where a.EmployeeID=@id and Year=@year order by ow_ID desc ";


            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("id", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repOther.DataSource = dt;
            repOther.DataBind();
        }

        public void BindService()
        {
            string sql = "select a.* from TeacherPublicService a where a.EmployeeID=@id and Year=@year order by Grade ";


            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("id", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repService.DataSource = dt;
            repService.DataBind();
        }

        public void BindTeaherPaper()
        {
            string sql = "select a.* from TeaherPaper a where a.EmployeeID=@id and Year=@year order by Grade ";


            DataAccessLibrary dal = new DataAccessLibrary();
            dal.ManagerParameter.AddNewInParameter("id", hidEmpoyeeID.Value);
            dal.ManagerParameter.AddNewInParameter("year", hidYear.Value);
            DataTable dt = dal.ExecuteFillDataTable(sql);

            repTeaherPaper.DataSource = dt;
            repTeaherPaper.DataBind();
        }

        public string getClass(string type)
        {
            string rtn = string.Empty;
            switch (type)
            {
                case "1":
                    rtn = "国家级";
                    break;
                case "2":
                    rtn = "省部级";
                    break;
                case "3":
                    rtn = "地方政府";
                    break;
                case "4":
                    rtn = "横向";
                    break;
            }
            return rtn;
        }



    }
}